﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
//using NPOI.XSSF.UserModel;
using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.XSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.IO;
namespace WindowsForms01
{
    public class inexcel
    {
        //A.BLL.newsContent content1 = new A.BLL.newsContent();
        #region 循环录数据进数据库通用方法
        public void insertToSql(DataTable M_dt,string tablename)//循环录入方法
        {
            try
            {
                if (M_dt.Rows.Count > 0)
                {
                    #region 获取每列的标题名称
                    string sqlselect = "select*from " + tablename;
                    DataTable dt = CsFormShow.GoSqlSelect(sqlselect);
                    List<string> list_colname = new List<string>();
                    string insertsql = "";
                    string val = "";
                    for (int i = 0; i < M_dt.Columns.Count; i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (M_dt.Columns[i].ColumnName == dt.Columns[j].ColumnName)
                            {
                                //dicval.Add(M_dt.Columns[i].ColumnName, "");
                                list_colname.Add(M_dt.Columns[i].ColumnName);
                            }
                        }
                    }
                    object[] arrparams = new object[list_colname.ToList().Count];
                    for (int i = 0; i < M_dt.Rows.Count; i++)
                    {
                        insertsql = "";
                        val = "";
                        for (int j = 0; j < list_colname.ToList().Count; j++)
                        {
                            //dicval[dicval.ToList()[j].Key] = M_dt.Rows[i][dicval.ToList()[j].Key].ToString().Trim();
                            if (list_colname.ToList().Count == j + 1)
                            {
                                insertsql += list_colname[j];
                                val += "'{" + j + "}'";
                                arrparams[j] = M_dt.Rows[i][list_colname[j]].ToString().Trim();
                                continue;
                            }
                            insertsql += list_colname[j] + ",";
                            val += "'{" + j + "}',";
                            arrparams[j] = M_dt.Rows[i][list_colname[j]].ToString().Trim();
                        }
                        insertsql = "insert into " + tablename + "(" + insertsql + ")values(" + val + ")";
                        insertsql = string.Format(insertsql, arrparams);
                        CsFormShow.GoSqlUpdateInsert(insertsql);
                    }
                    #endregion
                }

            }
            catch (Exception err)
            {
                Cslogfun.WriteToLog(err);
                throw new Exception("提示：" + err.Message);
            }
        }
        #endregion
        public void insertToSqlDr(DataRow M_dt, string tablename)//循环录入方法
        {
            try
            {

                    #region 获取每列的标题名称
                    string sqlselect = "select*from " + tablename;
                    DataTable dt = CsFormShow.GoSqlSelect(sqlselect);
                    List<string> list_colname = new List<string>();
                    string insertsql = "";
                    string val = "";
                    for (int i = 0; i < M_dt.Table.Columns.Count; i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (M_dt.Table.Columns[i].ColumnName== dt.Columns[j].ColumnName)
                            {
                                //dicval.Add(M_dt.Columns[i].ColumnName, "");
                                list_colname.Add(M_dt.Table.Columns[i].ColumnName);
                            }
                        }
                    }
                    object[] arrparams = new object[list_colname.ToList().Count];
                    for (int i = 0; i < M_dt.Table.Rows.Count; i++)
                    {
                        insertsql = "";
                        val = "";
                        for (int j = 0; j < list_colname.ToList().Count; j++)
                        {
                            //dicval[dicval.ToList()[j].Key] = M_dt.Rows[i][dicval.ToList()[j].Key].ToString().Trim();
                            if (list_colname.ToList().Count == j + 1)
                            {
                                insertsql += list_colname[j];
                                val += "'{" + j + "}'";
                                arrparams[j] = M_dt.Table.Rows[i][list_colname[j]].ToString().Trim();
                                continue;
                            }
                            insertsql += list_colname[j] + ",";
                            val += "'{" + j + "}',";
                            arrparams[j] = M_dt.Table.Rows[i][list_colname[j]].ToString().Trim();
                        }
                        insertsql = "insert into " + tablename + "(" + insertsql + ")values(" + val + ")";
                        insertsql = string.Format(insertsql, arrparams);
                        CsFormShow.GoSqlUpdateInsert(insertsql);
                    //string sqlstr = "insert into HHXSDH(TITLE,GOODNAME,CAIZHI,DOCODE,DPO,Market,SHIPDATE,COLOUR,PrepackNO,PREPACKSINGLE,BULKNO,Total,Supernumber,SCQTY,OUTQTY) values" +

                    //"('" + TITLE_V + "','" + GOODNAME_V + "','" + CAIZHI_V + "','" + DOCODE_V + "', '" + DPO_V + "', '" + Market_V + "','" + SHIPDATE_V + "', '" + COLOUR_V + "', '" + PrepackNO_V + "','" + PREPACKSINGLE_V + "','" + BULKNO_V + "', '" + Total_V + "', " + Supernumber_V + ", " + SCQTY_V + ", " + OUTQTY_V + ")";
                    //content1.Select_nothing(insertsql);
                    #endregion
                }

            }
            catch (Exception err)
            {
                Cslogfun.WriteToLog(err);
                throw new Exception("提示：" + err.Message);
            }
        }
        #region 循环录入订单数据进数据库
        public void insertCartonToSql(DataRow dr)//循环录入方法
        {
            try
            {
                #region 获取每列的标题名称
                string ColumnName_TITLE = dr.Table.Columns[0].ColumnName;
                string ColumnName_GOODNAME = dr.Table.Columns[1].ColumnName;
                string ColumnName_CAIZHI = dr.Table.Columns[2].ColumnName;
                string ColumnName_DOCODE = dr.Table.Columns[3].ColumnName;
                string ColumnName_DPO = dr.Table.Columns[4].ColumnName;
                string ColumnName_Market = dr.Table.Columns[5].ColumnName;
                string ColumnName_DESTINATION = dr.Table.Columns[6].ColumnName;
                string ColumnName_SHIPDATE = dr.Table.Columns[7].ColumnName;
                string ColumnName_COLOUR = dr.Table.Columns[8].ColumnName;
                string ColumnName_PrepackNO = dr.Table.Columns[9].ColumnName;
                string ColumnName_PREPACKSINGLE = dr.Table.Columns[10].ColumnName;
                string ColumnName_BULKNO = dr.Table.Columns[11].ColumnName;
                string ColumnName_Total = dr.Table.Columns[12].ColumnName;
                string ColumnName_Supernumber = dr.Table.Columns[13].ColumnName;
                string ColumnName_SCQTY = dr.Table.Columns[14].ColumnName;
                string ColumnName_OUTQTY = dr.Table.Columns[15].ColumnName;
                #endregion


                #region 循环录入数据 
                //excel表中的列名和数据库中的列名一定要对应  

                string TITLE_V = "";
                if (dr[ColumnName_TITLE].ToString() != "")
                {
                    TITLE_V = dr[ColumnName_TITLE].ToString().Trim();
                }
                string GOODNAME_V = "";

                if (dr[ColumnName_GOODNAME].ToString() != "")
                {
                    GOODNAME_V = dr[ColumnName_GOODNAME].ToString().Trim();
                }
                string CAIZHI_V = "";
                if (dr[ColumnName_CAIZHI].ToString() != "")
                {
                    CAIZHI_V = dr[ColumnName_CAIZHI].ToString().Trim();
                }
                string DOCODE_V = "";
                if (dr[ColumnName_DOCODE].ToString() != "")
                {
                    DOCODE_V = dr[ColumnName_DOCODE].ToString().Trim();
                }
                string DPO_V = "";
                if (dr[ColumnName_DPO].ToString() != "")
                {
                    DPO_V = dr[ColumnName_DPO].ToString().Trim();

                }
                string Market_V = "";
                if (dr[ColumnName_Market].ToString() != "")
                {
                    Market_V = dr[ColumnName_Market].ToString().Trim();
                }
                string DESTINATION_V = "";
                if (dr[ColumnName_DESTINATION].ToString() != "")
                {
                    DESTINATION_V = dr[ColumnName_DESTINATION].ToString().Trim();
                }
                string SHIPDATE_V = "";
                if (dr[ColumnName_SHIPDATE].ToString() != "")
                {
                    SHIPDATE_V = dr[ColumnName_SHIPDATE].ToString().Trim();
                }
                string COLOUR_V = "";
                if (dr[ColumnName_COLOUR].ToString() != "")
                {
                    COLOUR_V = dr[ColumnName_COLOUR].ToString().Trim();
                }
                string PrepackNO_V = "";
                if (dr[ColumnName_PrepackNO].ToString() != "")
                {
                    PrepackNO_V = dr[ColumnName_PrepackNO].ToString().Trim();

                }
                string PREPACKSINGLE_V = "";
                if (dr[ColumnName_PREPACKSINGLE].ToString() != "")
                {
                    PREPACKSINGLE_V = dr[ColumnName_PREPACKSINGLE].ToString().Trim();
                }

                string BULKNO_V = "";
                if (dr[ColumnName_BULKNO].ToString() != "")
                {
                    BULKNO_V = dr[ColumnName_BULKNO].ToString().Trim();
                }
                decimal Total_V = 0;
                if (dr[ColumnName_Total].ToString() != "")
                {
                    Total_V = decimal.Round(decimal.Parse(dr[ColumnName_Total].ToString()), 0);
                }

                decimal Supernumber_V = 0;
                if (dr[ColumnName_Supernumber].ToString() != "")
                {
                    Supernumber_V = decimal.Round(decimal.Parse(dr[ColumnName_Supernumber].ToString()), 0);
                }
                decimal SCQTY_V = 0;
                if (dr[ColumnName_SCQTY].ToString() != "")
                {
                    SCQTY_V = decimal.Round(decimal.Parse(dr[ColumnName_SCQTY].ToString()), 0);

                }
                decimal OUTQTY_V = 0;
                if (dr[ColumnName_OUTQTY].ToString() != "")
                {
                    OUTQTY_V = decimal.Round(decimal.Parse(dr[ColumnName_OUTQTY].ToString()), 0);

                }

                string sqlstr = "insert into HHXSDH(TITLE,GOODNAME,CAIZHI,DOCODE,DPO,Market,SHIPDATE,COLOUR,PrepackNO,PREPACKSINGLE,BULKNO,Total,Supernumber,SCQTY,OUTQTY) values" +

                "('" + TITLE_V + "','" + GOODNAME_V + "','" + CAIZHI_V + "','" + DOCODE_V + "', '" + DPO_V + "', '" + Market_V + "','" + SHIPDATE_V + "', '" + COLOUR_V + "', '" + PrepackNO_V + "','" + PREPACKSINGLE_V + "','" + BULKNO_V + "', '" + Total_V + "', " + Supernumber_V + ", " + SCQTY_V + ", " + OUTQTY_V + ")";
               CsFormShow.GoSqlUpdateInsert(sqlstr);

                #endregion
            }
            catch (Exception err)
            {
                Cslogfun.WriteToLog(err);
                throw new Exception("提示：" + err.Message);
            }

        }
        #endregion
        #region 获取excel第一个表名
        /// <summary>
        /// C#中获取Excel文件的第一个表名 
        /// Excel文件中第一个表名的缺省值是Sheet1$, 但有时也会被改变为其他名字. 如果需要在C#中使用OleDb读写Excel文件, 就需要知道这个名字是什么. 以下代码就是实现这个功能的:
        /// </summary>
        /// <param name="excelFileName"></param>
        /// <returns></returns>
        public string GetExcelFirstTableName(string excelFileName)
        {
            string tableName = null;
            try
            {
               
                if (File.Exists(excelFileName))
                {
                    using (OleDbConnection conn = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + excelFileName + ";" + "Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'"))
                    {
                        conn.Open();
                        DataTable M_dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        tableName = M_dt.Rows[1][2].ToString().Trim();
                        //for (int i = 0; i < M_dt.Rows.Count; i++)//循环获取所有表名
                        //{
                        //    tableName += M_dt.Rows[i][2].ToString().Trim() + ";";
                        //}
                    }
                }
              
            }
            catch (Exception err)
            {

                MessageBox.Show("操作失败！" + err.ToString());
            }
            return tableName;
        }
        #endregion



    }
}
